package cn.youframe.sample.tablist;

import cn.youframe.frame.base.BaseDaoImpl;
import cn.youframe.frame.util.SqlBuilder;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * User: Administrator
 * DateTime: 12-7-30: 下午3:50
 */
public class TabListDaoImpl extends BaseDaoImpl implements TabListDao {
    public DataSource getOraDataSource() {
        return OraDataSource;
    }

    public void setOraDataSource(DataSource oraDataSource) {
        OraDataSource = oraDataSource;
    }

    private DataSource OraDataSource;
    @Override
    public List<UserInfoVo> getUserInfo(Map<String,Object> paraMap) {
        SqlBuilder sbSql=new SqlBuilder();
        int[] startEnd=(int[])paraMap.get("ecStartEnd");
        String sortAlias=(String)paraMap.get("ecSortAlias");
        String sortType=(String)paraMap.get("ecSortType");
        //分页Sql begin++++++++++++++++++++++++++++++
        sbSql.append("select * from ( ");
        sbSql.append("select tmp.*, rownum rn from (");
        //分页Sql end-------------------------------
        //业务查询SQL 开始 ++++++++++++++++++++++++++++++++++++++++++++
        sbSql.append("select USERID,USERNAME,REGDATE,EMAIL,MEMO from user_info ");
        if(sortAlias!=null){
            sbSql.append(" order by ").append(sortAlias).append(" ").append(sortType);
        }
        sbSql.append(" ) tmp ");
        //业务查询SQL 结束 ------------------------------------------------
        sbSql.append("where rownum < ?) where rn >= ?");
        sbSql.addArg(startEnd[1]);
        sbSql.addArg(startEnd[0]);
        //排序需要的sql 结束
        List<UserInfoVo> lsvo=getJdbcTemplate().query(sbSql.getSQL(), BeanPropertyRowMapper.newInstance(UserInfoVo.class),sbSql.getArgArray());
//        List<UserInfoVo> lsvo=getJdbcTemplate().queryForList(sbSql.getSQL(),UserInfoVo.class,sbSql.getArgArray());
        return lsvo;
    }

    @Override
    public int getUserNum() {
        SqlBuilder sqlbu=new SqlBuilder();
        sqlbu.append("select count(*) from user_info");
        int ret=getJdbcTemplate().queryForObject(sqlbu.getSQL(),Integer.class);
        return ret;
    }

    @Override
    public List<LogVo> queryLogOracle(Map<String, Object> paraMap) {
        SqlBuilder sbSql=new SqlBuilder();
        int[] startEnd=(int[])paraMap.get("ecStartEnd");
        String sortAlias=(String)paraMap.get("ecSortAlias");
        String sortType=(String)paraMap.get("ecSortType");

        String workNo=(String)paraMap.get("workNo");//账号
        String menuName=(String)paraMap.get("menuName");//菜单名
        String oprDesc=(String)paraMap.get("oprDesc");//操作描述
        String beginTime=(String)paraMap.get("beginTime");//开始时间
        String endTime=(String)paraMap.get("endTime");//结束时间
        //分页Sql begin++++++++++++++++++++++++++++++
        sbSql.append("select * from ( ");
        sbSql.append("select tmp.*, rownum rn from (");
        //分页Sql end-------------------------------
        //业务查询SQL 开始 ++++++++++++++++++++++++++++++++++++++++++++
        sbSql.append("SELECT F_WORK_NO,F_MENU_NAME,F_OPERATE_DESC,TO_CHAR(F_OPERATE_DATE, 'YYYY-MM-DD HH24:MI:SS') F_OPERATE_DATE,count(*)over() as TOTAL_NUMS ");
        sbSql.append(" FROM OM_WRITE_LOG_T WHERE 1=1 ");
        if (workNo != null && workNo.trim().length() != 0) {
            sbSql.append(" AND F_WORK_NO=? ");
            sbSql.addArg(workNo);
        }
        if (menuName != null && menuName.trim().length() != 0) {
            sbSql.append(" AND INSTR(F_MENU_NAME,?)>0");
            sbSql.addArg(menuName);
        }
        if (oprDesc != null && oprDesc.trim().length() != 0) {
            sbSql.append(" AND INSTR(F_OPERATE_DESC,?)>0 ");
            sbSql.addArg(oprDesc);
        }
        if (beginTime != null && beginTime.trim().length() != 0) {
            sbSql.append("AND F_OPERATE_DATE >= TO_DATE(?, 'YYYY-MM-DD HH24:MI') ");
            sbSql.addArg(beginTime);
        }
        if (endTime != null && endTime.trim().length() != 0) {
            sbSql.append("AND F_OPERATE_DATE <= TO_DATE(?, 'YYYY-MM-DD HH24:MI') ");
            sbSql.addArg(endTime);
        }
        if(sortAlias!=null){
            sbSql.append(" order by ").append(sortAlias).append(" ").append(sortType);
        }
        sbSql.append(" ) tmp ");
        //业务查询SQL 结束 ------------------------------------------------
        sbSql.append("where rownum < ?) where rn >= ?");
        sbSql.addArg(startEnd[1]);
        sbSql.addArg(startEnd[0]);
        List<LogVo> logLs=createJdbcTemplate(OraDataSource).query(sbSql.getSQL(),sbSql.getArgArray(),new RowMapper<LogVo>() {
            @Override
            public LogVo mapRow(ResultSet rs, int i) throws SQLException {
                LogVo logVo=new LogVo();
                logVo.setWorkNo(rs.getString("F_WORK_NO"));
                logVo.setMenuName(rs.getString("F_MENU_NAME"));
                logVo.setOperateDesc(rs.getString("F_OPERATE_DESC"));
                logVo.setOperateDate(rs.getString("F_OPERATE_DATE"));
                logVo.setTotalNums(rs.getInt("TOTAL_NUMS"));
                return logVo;
            }
        });
        return logLs;
    }
}
